<?php
ini_set('memory_limit', '300M');

include_once 'config/env.config.php';
include_once 'config/db.'.Env_Config.'.config.php';

$dbname2 = 'tracking';

// CMS DB
$options = array(PDO::ATTR_PERSISTENT=>FALSE);
$conn = new PDO('mysql:host='.dbHost.';dbname='.dbName, dbUser, dbPwd, $options);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$conn->exec('SET NAMES ' . dbCharset);

// tracking DB
$options = array(PDO::ATTR_PERSISTENT=>FALSE);
$conn2 = new PDO('mysql:host='.dbHost.';dbname='.$dbname2, dbUser, dbPwd, $options);
$conn2->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$conn2->exec('SET NAMES ' . dbCharset);

// get table name from tracking DB
$kwTableArr = array();
$picTableArr = array();
$getTableName_sql = "show tables";
$rows = $conn2->query($getTableName_sql)->fetchAll(PDO::FETCH_ASSOC);
foreach ($rows as $row){
	if(stripos($row['table_in_tracking'], 'kw_imp')!==false){
		$kwTableArr[] = $row['table_in_tracking'];
	}
	if(stripos($row['table_in_tracking'], 'img_imp')!==false){
		$picTableArr[] = $row['table_in_tracking'];
	}
		
}

// check KW launch
$now = date('Y-m-d H:i:s');
$get_kwlist_sql = "select * from tbl_launch WHERE type = 'keywords' AND (start <= '{$now}' AND end >= '{$now}') AND is_effect = '0' ORDER BY weight DESC";
$kw_rows = $conn->query($get_kwlist_sql)->fetchAll(PDO::FETCH_ASSOC);

foreach ($kw_rows as $row) {
	echo $row['id'].'|keywords  ';
	checkData($row['id'], 'keywords', $kwTableArr, $row['num']);
}

unset($kw_rows);

// check Pic launch
$get_piclist_sql = "select * from tbl_launch_picture WHERE  (start <= '{$now}' AND end >= '{$now}') AND is_effect = '0' ORDER BY weight DESC";
$pic_rows = $conn->query($get_piclist_sql)->fetchAll(PDO::FETCH_ASSOC);

foreach ($pic_rows as $row) {
	echo $row['id'].'|pic  ';
	checkData($row['id'], 'pic', $picTableArr, $row['num']);
}

unset($pic_rows);

$today = date('Y-m-d');
// get All temp Data 
$get_today_temp_outline_sql = "select * from tbl_temp_log_num where is_outline = '1' and op_st like '$today%' ";
$today_temp_rows = $conn->query($get_today_temp_outline_sql)->fetchAll(PDO::FETCH_ASSOC);
// change launch's effect status
foreach ($today_temp_rows as $row){
	changeEffect($row['launch_type'], $row['launch_id']);
}

exit;	//done


/*************************************************************************************************************/

function getAllDateNum($type, $tableArr){
	global $conn2 ;
	$now = date('Ymd');
	if($type=='keywords'){
		$tbl_prefix = 'kw_imp';
// 		$select_sql = "select adid as key_id, count(*) as c from $table_name group by key_id order by c desc";
		$select_sql_a = "select adid as key_id, count(*) as c from ";
		$select_sql_b = " group by key_id order by c desc";
	}else{
		$tbl_prefix = 'img_imp';
// 		$select_sql = "select adurl as key_id, count(*) as c from $table_name group by key_id order by c desc";
		$select_sql_a = "select adurl as key_id, count(*) as c from ";
		$select_sql_b = " group by key_id order by c desc";
	}
	
	$allList = array();
	foreach ($tableArr as $table_name){
		if(stripos($table_name, $tbl_prefix) !== false && $table_name!=($tbl_prefix.'_'.$now)){
			$select_sql = $select_sql_a.$table_name.$select_sql_b;
			$rows = $conn2->query($select_sql)->fetchAll(PDO::FETCH_ASSOC);
			foreach ($rows as $row){
				$allList[$row['key_id']] = intval($allList[$row['key_id']]) + intval($row['s']);
			}
		}
	}
	
	return $allList;
}

function getAllDateLogNumByID($type, $tableArr, $id){
	global $conn2 ;
	$now = date('Ymd');
	if($type=='keywords'){
		$tbl_prefix = 'kw_imp';
// 		$select_sql = "select adid as key_id, count(*) as c from $table_name group by key_id order by c desc";
		$select_sql_a = "select adid as key_id, count(*) as c from ";
		$select_sql_b = " where key_id like '%$id%' group by key_id order by c desc";
	}else{
		$tbl_prefix = 'img_imp';
// 		$select_sql = "select adurl as key_id, count(*) as c from $table_name group by key_id order by c desc";
		$select_sql_a = "select adurl as key_id, count(*) as c from ";
		$select_sql_b = " where key_id like '%$id%' group by key_id order by c desc";
	}
	
	$allNumber = 0;
	foreach ($tableArr as $table_name){
		if(stripos($table_name, $tbl_prefix) !== false && $table_name!=($tbl_prefix.'_'.$now)){
			$select_sql = $select_sql_a.$table_name.$select_sql_b;
			$rows = $conn2->query($select_sql)->fetchAll(PDO::FETCH_ASSOC);
			foreach ($rows as $row){
				if(stripos($row['key_id'], $id)!==false){
					$allNumber += intval($row['s']);
				}
			}
			unset($rows);
		}
	}
	
	return $allNumber;
}

function getOneDayLogNumByID($type, $id, $tableArr, $date='now'){
	global $conn2 ;
	
	if($date=='now'){
		$date = date('Ymd');
	}
	
	if($type=='keywords'){
		$table_name = 'kw_imp_'.$date;
// 		$select_sql = "select adid as key_id, count(*) as c from $table_name group by key_id order by c desc";
		$select_sql_a = "select adid as key_id, count(*) as c from ";
		$select_sql_b = " where key_id like '%$id%' group by key_id order by c desc";
	}else{
		$table_name = 'img_imp_'.$date;
// 		$select_sql = "select adurl as key_id, count(*) as c from $table_name group by key_id order by c desc";
		$select_sql_a = "select adurl as key_id, count(*) as c from ";
		$select_sql_b = " where key_id like '%$id%' group by key_id order by c desc";
	}
	
	if(!in_array($table_name, $tableArr))return 0;
	
	$allNumber = 0;
	$select_sql = $select_sql_a.$table_name.$select_sql_b;
	$rows = $conn2->query($select_sql)->fetchAll(PDO::FETCH_ASSOC);
	foreach ($rows as $row){
		if(stripos($row['key_id'], $id)!==false){
			$allNumber = intval($row['s']);
		}
	}
	
	return $allNumber;
}

// check temp Data 
function checkData($id, $type, $tableArr, $outline){
	global $conn;
	
	$yesterday = date('Ymd', strtotime('-1 day'));
	$today_num = getOneDayLogNumByID($type, $id, $tableArr);	//get today num
	
	$select_sql = "select * from tbl_temp_log_num where launch_type = '$type' and launch_id='$id' ";
	
	$temp_row = $conn->query($select_sql)->fetch(PDO::FETCH_ASSOC);
	// if first time
	if(!isset($temp_row['id']) && empty($temp_row['id'])){
		$last_num = getAllDateLogNumByID($type, $tableArr, $id);
		
		$previous_date = '';
		if($last_num>0){
			$previous_date = $yesterday; 
		}
		
		$is_outline = 0; 
		if(($last_num+$today_num) >= $is_outline){
			$is_outline = 1;
		}
		
		$now_num = $last_num+$today_num;
		
		$now = date('Y-m-d H:i:s');
		$insert_sql = "insert into tbl_temp_log_num (`launch_type`, `launch_id`, `previous_num`, `now_num`, `previous_date`, `is_outline`, `op_st`) values ('$type', '$id', '$last_num', '$now_num', '$previous_date', '$is_outline','$now')";
		$conn->exec($insert_sql);
		
		return ;
	}
	
	if($temp_row['previous_date'] != $yesterday){
		$last_num = getAllDateLogNumByID($type, $tableArr, $id);
		$previous_date = $yesterday;
	}else{
		$last_num = $temp_row['previous_num'];
		$previous_date = $temp_row['previous_date'];
	}
	
	$is_outline = 0;
	if(($last_num+$today_num) >= $is_outline){
		$is_outline = 1;
	}
	
	$now_num = $last_num+$today_num;
	
	$now = date('Y-m-d H:i:s');
	$update_sql = "update tbl_temp_log_num set `previous_num` = '$last_num' , `now_num` = '$now_num', `previous_date` = '$previous_date', `is_outline` = '$is_outline', `op_st` = '$now' where  `launch_type` = '$type' and  `launch_id` = '$id' ";
	$conn->exec($update_sql);
	return ;
}

function changeEffect($type, $id){
	global $conn, $conn2;
	$sql = "";
	if($type=='keywords'){
		$sql = "update tbl_launch set is_effect = '1' where type = '$type' and id = '$id' ";
	}else{
		$sql = "update tbl_launch_picture set is_effect = '1' where id = '$id' ";
	}
	
	$affect = $conn->exec($sql);
	if($affect){
		$update_sql = "update tbl_temp_log_num set is_outline = '2' where launch_type='$type' and launch_id = '$id' ";
		$conn->exec($update_sql);
	}
}
